package com.infore.siteManagement.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.infore.siteManagement.entity.Station;

/**
 * 站点仓库
 * 
 * @author 刘乐法
 *
 *
 */
@Repository
public interface IStationRepository extends JpaRepository<Station, String>, JpaSpecificationExecutor<Station> {

	@Modifying
	@Transactional
	int deleteByStationIdIn(List<String> ids);

	/** 插入相关站点数据 **/
	@Modifying
	@Transactional
	@Query(value = "INSERT INTO STATION(STATION_ID,STATION_MN,STATION_NAME,AREA_ID,BASINN_ID,CONTROL_LEVEL,LONGITUDE,LATITUDE,STATION_ADDRESS,STATION_TYPE1,TARGER_WATER_QUALITY,MONITOR_STATUS,ENABLED,NETWORKING,CITY,CITY_NAME,AREA_NAMES)"
			+ " VALUES (:stationId,:stationCode,:stationName,:areaId,:basinId,:controlorLevel,:longitude,:latitude,:stationAddress,:stationType,:targetLevel,:monitorStatus,:enabled,:networking,:city,:cityName,:areaName)", nativeQuery = true)
	int insertStationInfo(@Param("stationId") String stationId, @Param("stationCode") String stationCode,
			@Param("stationName") String stationName, @Param("areaId") String areaId, @Param("basinId") String basinId,
			@Param("controlorLevel") Integer controlorLevel, @Param("longitude") Double longitude,
			@Param("latitude") Double latitude, @Param("stationAddress") String stationAddress,
			@Param("stationType") String stationType, @Param("targetLevel") String targetLevel,
			@Param("monitorStatus") String monitorStatus, @Param("enabled") String enabled,
			@Param("networking") String networking, @Param("city") String city, @Param("cityName") String cityName,
			@Param("areaName") String areaName);

	/** 更新相关站点数据 **/
	@Modifying
	@Transactional
	@Query(value = "UPDATE STATION SET STATION_MN = :stationCode, "
			+ "STATION_NAME = :stationName,AREA_ID = :areaId, BASINN_ID = :basinId,CONTROL_LEVEL = :controlorLevel,"
			+ "LONGITUDE = :longitude, LATITUDE = :latitude, STATION_ADDRESS = :stationAddress, "
			+ "STATION_TYPE1 = :stationType, TARGER_WATER_QUALITY = :targetLevel, CITY = :city, CITY_NAME = :cityName, AREA_NAMES = :areaName WHERE STATION_ID = :stationId", nativeQuery = true)
	int updateStationInfo(@Param("stationCode") String stationCode, @Param("stationName") String stationName,
			@Param("areaId") String areaId, @Param("basinId") String basinId,
			@Param("controlorLevel") Integer controlorLevel, @Param("longitude") Double longitude,
			@Param("latitude") Double latitude, @Param("stationAddress") String stationAddress,
			@Param("stationType") String stationType, @Param("targetLevel") String targetLevel,
			@Param("city") String city, @Param("cityName") String cityName, @Param("areaName") String areaName,
			@Param("stationId") String stationId);

	/** 查询相关站点数据 **/
	@Query(value = "SELECT a.STATION_ID,\n" + "       a.STATION_MN,\n" + "       a.STATION_NAME,\n"
			+ "       a.CONTROL_LEVEL,\n" + "       a.LONGITUDE,\n" + "       a.LATITUDE,\n"
			+ "       a.STATION_ADDRESS,\n" + "       a.STATION_TYPE1,\n" + "       a.TARGER_WATER_QUALITY,\n"
			+ "       a.CITY,\n" + "       a.CITY_NAME,\n" + "       a.AREA_ID,\n" + "       a.AREA_NAMES,\n"
			+ "       a.OPER_COMPANY_NAME,\n" + "       b.basinn_code,\n" + "       a.basinn_name,\n"
			+ "       ar.area_code\n" + "  FROM STATION a\n" + "  left join area ar on ar.area_id = a.area_id\n"
			+ "  left join BASINN b\n" + "    on a.basinn_id = b.basinn_id\n"
			+ " where a.STATION_MN = :stationCode", nativeQuery = true)
	List<Map<String, Object>> selectStationInfo(@Param("stationCode") String stationCode);

	// 查询站点图片外景
	@Query(value = "select t.out_image from STATION t  where t.station_mn = :stationCode", nativeQuery = true)
	String selectOutImageInfo(@Param("stationCode") String stationCode);

	// 查询站点图片内景
	@Query(value = "select t.inside_image from STATION t  where t.station_mn = :stationCode", nativeQuery = true)
	String selectInsideImageInfo(@Param("stationCode") String stationCode);

	// 查询站点图片建设
	@Query(value = "select t.build_image from STATION t  where t.station_mn = :stationCode", nativeQuery = true)
	String selectBuildImageInfo(@Param("stationCode") String stationCode);

	// 查询站点下所有图片
	@Query(value = "select t.out_image,t.inside_image,t.build_image from STATION t where t.station_mn = :stationCode", nativeQuery = true)
	List<Map<String, Object>> selectAllImage(@Param("stationCode") String stationCode);

	// 查询站点下所有图片----用于删除
	@Query(value = "select t.out_image,t.inside_image,t.build_image from STATION t where t.station_id in (:ids)", nativeQuery = true)
	List<Map<String, Object>> selectAllImageByStationId(@Param("ids") List<String> ids);

	// 更新站点图片外景
	@Modifying
	@Transactional
	@Query(value = "update STATION t set t.out_image = :imageName where t.station_mn = :mn ", nativeQuery = true)
	int updateImageInfo(@Param("mn") String mn, @Param("imageName") String imageName);

	// 更新站点图片内景
	@Modifying
	@Transactional
	@Query(value = "update STATION t set t.inside_image = :imageName where t.station_mn = :mn ", nativeQuery = true)
	int updateImageInside(@Param("mn") String mn, @Param("imageName") String imageName);

	// 更新站点图片建设
	@Modifying
	@Transactional
	@Query(value = "update STATION t set t.build_image = :imageName where t.station_mn = :mn ", nativeQuery = true)
	int updateImageBuild(@Param("mn") String mn, @Param("imageName") String imageName);

	// 通过查询信息的到站点编码
	@Query(value = "select t.station_mn  from STATION t where t.station_name in (:stationNamesList) and t.area_id in (:areaCodeList) and t.basinn_id in (:basinCodeList) and t.station_type_id in (:stationTypeList) and t.station_type1 in (:stationType1List)", nativeQuery = true)
	List<String> findStationMnByInfo(@Param("areaCodeList") List<String> areaCodeList,
			@Param("basinCodeList") List<String> basinCodeList, @Param("stationTypeList") List<String> stationTypeList,
			@Param("stationType1List") List<String> stationType1List,
			@Param("stationNamesList") List<String> stationNamesList);

//	//
	// @EntityGraph(value = "station_view")根据区域id和站点名模糊查询
//	@Query(value = "select * from station t where 1=1 and (t.area_id = :areaId or nvl (:areaId,'1')='1') and (t.station_name like :stationName or nvl (:stationName,'1')='1') order by t.sort asc ", nativeQuery = true)
//	List<Station> selectStationByAreaIdAndStationName(@Param("areaId") String areaId,@Param("stationName") String stationName);

	// 插入站点完整信息
	@Modifying
	@Transactional
	@Query(value = "INSERT INTO STATION (AREA_ID,AREA_NAMES,BASINN_ID,BASINN_NAME,"
			+ "CITY_ID,CITY_NAME,CONTROL_LEVEL,ENABLED,ENABLED_TIME,LATITUDE,"
			+ "LOCAL_MANGERMNET_UNITS,LONGITUDE,PROVINCE_ID,PROVINCE_NAME,"
			+ "SORT,STATION_ADDRESS,STATION_MN,STATION_ID,STATION_NAME,"
			+ "STATION_PASSWORLD,STATION_PROPERTY,STATION_PROVINCES_UP_DOWN,"
			+ "STATION_TYPE_ID,STATION_CLASSIFY_ID,TARGER_WATER_QUALITY,STATION_RIVER,"
			+ "STATION_FUNCTION,OPER_COMPANY_ID,OPER_COMPANY_NAME,STATION_BRIEF_INFO,"
			+ "PERIPHERAL_HISTORY,SUIFACE_WATER_TYPES,data_frequency,station_type1,KEY_PERSON,"
			+ "is_special_business,kil,station_level,station_leader_id,station_leader," + "opcen_id,opcen_name) "
			+ " VALUES (:areaid,:areaName,:basinId,:basinName,:city,:cityName,:controlorLevel,"
			+ ":enabled,:enabledTimeDate,:latitudeFloat,:localManagementUnits,:longitudeFloat,"
			+ ":provinceId,:provinceName,:sort,:stationAddress,:stationCode,:stationId,:stationName,"
			+ ":stationPassworld,:stationProperty,:stationProvincesUpDown,:stationType,:stationClassify,"
			+ ":targetLevel,:stationRiver,:stationFunction,:operCompanyId,:operCompanyName,:stationBriefInfo,"
			+ ":peripheralHistory,:surfaceWaterTypes,:dataFrequency,:stationType1,:keyPerson,:isSpecialBusiness,"
			+ ":kil,:stationLevel,:stationLeaderId,:stationLeader,:opcenId,:opcenName)", nativeQuery = true)
	int insertStationAllInfo(@Param("areaid") String areaid, @Param("areaName") String areaName,
			@Param("basinId") String basinId, @Param("basinName") String basinName, @Param("city") String city,
			@Param("cityName") String cityName, @Param("controlorLevel") String controlorLevel,
			@Param("enabled") String enabled, @Param("enabledTimeDate") Date enabledTimeDate,
			@Param("latitudeFloat") Float latitudeFloat, @Param("localManagementUnits") String localManagementUnits,
			@Param("longitudeFloat") Float longitudeFloat, @Param("provinceId") String provinceId,
			@Param("provinceName") String provinceName, @Param("sort") String sort,
			@Param("stationAddress") String stationAddress, @Param("stationCode") String stationCode,
			@Param("stationId") String stationId, @Param("stationName") String stationName,
			@Param("stationPassworld") String stationPassworld, @Param("stationProperty") String stationProperty,
			@Param("stationProvincesUpDown") String stationProvincesUpDown, @Param("stationType") String stationType,
			@Param("stationClassify") String stationClassify, @Param("targetLevel") String targetLevel,
			@Param("stationRiver") String stationRiver, @Param("stationFunction") String stationFunction,
			@Param("operCompanyId") String operCompanyId, @Param("operCompanyName") String operCompanyName,
			@Param("stationBriefInfo") String stationBriefInfo, @Param("peripheralHistory") String peripheralHistory,
			@Param("surfaceWaterTypes") String surfaceWaterTypes, @Param("dataFrequency") String dataFrequency,
			@Param("stationType1") String stationType1, @Param("keyPerson") String keyPerson,
			@Param("isSpecialBusiness") String isSpecialBusiness, @Param("kil") String kil,
			@Param("stationLevel") String stationLevel, @Param("stationLeaderId") String stationLeaderId,
			@Param("stationLeader") String stationLeader, @Param("opcenId") String opcenId,
			@Param("opcenName") String opcenName);

	// 更新站点完整信息
	@Modifying
	@Transactional
	@Query(value = "UPDATE STATION T SET "
			+ "AREA_ID=:areaid,AREA_NAMES=:areaName,BASINN_ID=:basinId,BASINN_NAME=:basinName,"
			+ "CITY_ID=:city,CITY_NAME=:cityName,CONTROL_LEVEL=:controlorLevel,ENABLED=:enabled,"
			+ "ENABLED_TIME=:enabledTimeDate,LATITUDE=:latitudeFloat,LOCAL_MANGERMNET_UNITS=:localManagementUnits,"
			+ "LONGITUDE=:longitudeFloat,PROVINCE_ID=:provinceId,PROVINCE_NAME=:provinceName,SORT=:sort,"
			+ "STATION_ADDRESS=:stationAddress,STATION_MN=:stationCode,STATION_NAME=:stationName,"
			+ "STATION_PASSWORLD=:stationPassworld,STATION_PROPERTY=:stationProperty,STATION_PROVINCES_UP_DOWN=:stationProvincesUpDown,"
			+ "STATION_TYPE_ID=:stationType,STATION_CLASSIFY_ID=:stationClassify,TARGER_WATER_QUALITY=:targetLevel,"
			+ "STATION_RIVER=:stationRiver,STATION_FUNCTION=:stationFunction,OPER_COMPANY_ID=:operCompanyId,"
			+ "OPER_COMPANY_NAME=:operCompanyName,STATION_BRIEF_INFO=:stationBriefInfo,PERIPHERAL_HISTORY=:peripheralHistory,"
			+ "SUIFACE_WATER_TYPES=:surfaceWaterTypes,data_frequency=:dataFrequency,station_type1=:stationType1,"
			+ "KEY_PERSON=:keyPerson,is_special_business=:isSpecialBusiness,kil=:kil,station_level=:stationLevel,"
			+ "station_leader_id=:stationLeaderId,station_leader=:stationLeader,opcen_id=:opcenId,opcen_name=:opcenName "
			+ " WHERE STATION_ID=:stationId ", nativeQuery = true)
	int updateStationAllInfo(@Param("areaid") String areaid, @Param("areaName") String areaName,
			@Param("basinId") String basinId, @Param("basinName") String basinName, @Param("city") String city,
			@Param("cityName") String cityName, @Param("controlorLevel") String controlorLevel,
			@Param("enabled") String enabled, @Param("enabledTimeDate") Date enabledTimeDate,
			@Param("latitudeFloat") Float latitudeFloat, @Param("localManagementUnits") String localManagementUnits,
			@Param("longitudeFloat") Float longitudeFloat, @Param("provinceId") String provinceId,
			@Param("provinceName") String provinceName, @Param("sort") String sort,
			@Param("stationAddress") String stationAddress, @Param("stationCode") String stationCode,
			@Param("stationId") String stationId, @Param("stationName") String stationName,
			@Param("stationPassworld") String stationPassworld, @Param("stationProperty") String stationProperty,
			@Param("stationProvincesUpDown") String stationProvincesUpDown, @Param("stationType") String stationType,
			@Param("stationClassify") String stationClassify, @Param("targetLevel") String targetLevel,
			@Param("stationRiver") String stationRiver, @Param("stationFunction") String stationFunction,
			@Param("operCompanyId") String operCompanyId, @Param("operCompanyName") String operCompanyName,
			@Param("stationBriefInfo") String stationBriefInfo, @Param("peripheralHistory") String peripheralHistory,
			@Param("surfaceWaterTypes") String surfaceWaterTypes, @Param("dataFrequency") String dataFrequency,
			@Param("stationType1") String stationType1, @Param("keyPerson") String keyPerson,
			@Param("isSpecialBusiness") String isSpecialBusiness, @Param("kil") String kil,
			@Param("stationLevel") String stationLevel, @Param("stationLeaderId") String stationLeaderId,
			@Param("stationLeader") String stationLeader, @Param("opcenId") String opcenId,
			@Param("opcenName") String opcenName);

	/** 自动站多站得到站点名称 **/
	@Query(value = "select targer_water_quality as ARMLEVEL,t.station_mn as MN ,t.station_name as NAME,t.basinn_name,t.province_name "
			+ "from STATION t where t.station_mn in(:mn)", nativeQuery = true)
	List<Map<String, Object>> queryStationNameByMn(@Param("mn") List<String> mn);

	/** 自动站多站得到站点因子发送频率 **/
	@Query(value = "select t.project_code,t.data_frequency from PROJECT_AND_STATION t where  t.station_code in (:mn) and t.project_code = :projId", nativeQuery = true)
	List<Map<String, Object>> queryFrequencyByStationMn(@Param("mn") List<String> mn, @Param("projId") String projId);

	/** 自动站多站得到站点因标准值 **/
	@Query(value = "select t.project_code,t.standard from PROJECT_AND_STATION t where  t.station_code in (:mn) and t.project_code = :projId", nativeQuery = true)
	List<Map<String, Object>> queryStandardByStationMn(@Param("mn") List<String> mn, @Param("projId") String projId);

	/** 区域内的所有站点部分信息---流域名称,站点名称，河流，mn，区域名称，目标等级 **/
	@Query(value = "select s.station_id as STATION_ID, s.station_name as STATIONNAME,a.area_name AS AREANAME,s.targer_water_quality as ARMLEVEL,s.station_river as RIVER,s.station_mn as MN,s.data_frequency as FREQUENCY  from Station s "
			+ "left join AREA a on s.area_id = a.area_id " + "where s.area_id in ("
			+ "select t.area_id from AREA t start with t.area_code in (:areaCode) connect by prior t.area_code=t.parent_id)", nativeQuery = true)
	List<Map<String, Object>> queryStationInfoByFatherArea(@Param("areaCode") List<String> areaCode);

	/** 站点部分信息---流域名称,站点名称，河流，mn，区域名称，目标等级 **/
	@Query(value = "select s.station_id as STATION_ID, s.station_name as STATIONNAME,"
			+ "s.targer_water_quality as ARMLEVEL,s.station_river as RIVER,s.station_mn as MN,"
			+ "s.data_frequency as FREQUENCY,(select count(1) from PROJECT_AND_STATION t where t.station_code = s.station_mn) as nums "
			+ " from Station s where station_mn in (:mns)", nativeQuery = true)
	List<Map<String, Object>> queryStationInfoByMns(@Param("mns") List<String> mns);

	/** 流域内的所有站点 **/
	@Query(value = "select s.station_id as STATION_ID,s.station_name as STATIONNAME,b.basinn_name AS BASINNNAME,s.targer_water_quality as ARMLEVEL,s.station_river as RIVER,s.station_mn as MN,s.data_frequency as FREQUENCY  from Station s "
			+ "left join Basinn b on s.basinn_id = b.basinn_id where s.basinn_id in ( "
			+ "select n.basinn_id from  Basinn n where n.basinn_code in (:basinnCode) )", nativeQuery = true)
	List<Map<String, Object>> queryStationInfoByFatherBasinn(@Param("basinnCode") List<String> basinnCode);

	@Query(value = "select s.station_mn from station s where s.station_id  in (select t.station_id from PLATFORM_CUSTOMER_STATION t where t.platform_id = :platformId)", nativeQuery = true)
	List<String> queryBaiscStationInfoById(@Param("platformId") String platformId);

	@EntityGraph(value = "station_view")
	List<Station> findAll(Specification<Station> spec);

	@EntityGraph(value = "station_view")
	List<Station> findAll();

	@EntityGraph(value = "station_view")
	Page<Station> findAll(Pageable pageable);

	@EntityGraph(value = "station_view")
	Page<Station> findAll(Specification<Station> spec, Pageable pageable);

	@EntityGraph(value = "station_view")
	List<Station> findAllByStationIdIn(List<String> id);

	@EntityGraph(value = "station_view")
	Station findOneByStationId(String stationId);

	@EntityGraph(value = "station_view")
	Station findOneByStationMn(String mn);

	@EntityGraph(value = "station_view")
	List<Station> findAllByStationMnIn(List<String> stationMnList);

	/** 查询站点基础信息，包含区域名称，流域名称，不含省份城市 **/
	@Query(value = "select t.TARGER_WATER_QUALITY,t.station_name,t.longitude,t.latitude,t.station_address,t.build_time,t.station_leader,t.leader_tel,t.monitor_status,t.enabled,t.sort, "
			+ "t.control_level,t.station_id,t.auto_flag,t.manuel_flag,t.public_flag,t.oper_company_name,t.oper_company_leader,t.oper_company_leader_tel,t.station_leader_id, "
			+ "t.networking,t.station_mn,t.station_passworld,t.station_property,t.station_provinces_up_down,t.station_type1,t.user_id,t.enabled_time, "
			+ "t.local_mangermnet_units,t.station_river,t.station_function,t.station_brief_info,t.suiface_water_types,t.oper_company_id,t.data_frequency, "
			+ "a.area_code,a.area_name,a.aim_water_level as area_aim,a.area_id,a.parent_id,a.area_level_id,b.basinn_id,b.basinn_name,b.basinn_code,b.aim_water_level as basinn_aim, "
			+ "ty.station_type_id,ty.station_type_name,ty.station_type_code,cla.station_classify_id,cla.station_classify_name,cla.station_classify_code "
			+ "from station t left join area a on t.area_id = a.area_id left join basinn b on t.basinn_id = b.basinn_id left join station_type ty on t.station_type_id=ty.station_type_id "
			+ "left join station_classify cla on t.station_classify_id = cla.station_classify_id "
			+ "where t.station_id in (:stationIds)", nativeQuery = true)
	List<Map<String, Object>> getStationBasicById(@Param("stationIds") List<String> stationIds);

	/** 查询站点基础信息，包含区域名称，流域名称，不含省份城市 **/
	@Query(value = "select t.TARGER_WATER_QUALITY,t.station_name,t.longitude,t.latitude,t.station_address,t.build_time,t.station_leader,t.leader_tel,t.monitor_status,t.enabled,t.sort, "
			+ "t.control_level,t.station_id,t.auto_flag,t.manuel_flag,t.public_flag,t.oper_company_name,t.oper_company_leader,t.oper_company_leader_tel,t.station_leader_id, "
			+ "t.networking,t.station_mn,t.station_passworld,t.station_property,t.station_provinces_up_down,t.station_type1,t.user_id,t.enabled_time, "
			+ "t.local_mangermnet_units,t.station_river,t.station_function,t.station_brief_info,t.suiface_water_types,t.oper_company_id,t.data_frequency, "
			+ "a.area_code,a.area_name,a.aim_water_level as area_aim,a.area_id,a.parent_id,a.area_level_id,b.basinn_id,b.basinn_name,b.basinn_code,b.aim_water_level as basinn_aim, "
			+ "ty.station_type_id,ty.station_type_name,ty.station_type_code,cla.station_classify_id,cla.station_classify_name,cla.station_classify_code "
			+ "from station t left join area a on t.area_id = a.area_id left join basinn b on t.basinn_id = b.basinn_id left join station_type ty on t.station_type_id=ty.station_type_id "
			+ "left join station_classify cla on t.station_classify_id = cla.station_classify_id", nativeQuery = true)
	List<Map<String, Object>> getStationBasicAll();

	/** 添加合同时查询站点和运维公司负责人部分信息，通过stationId得到stationMn **/
	@Query(value = "select station_name,oper_company_name ,oper_company_id ,station_mn ,station_leader,station_leader_id from station where station_id in (:ids)", nativeQuery = true)
	List<Map<String, Object>> getStationMnBasicById(@Param("ids") List<String> ids);

	/** 备品备件查询仪器名称站点编号 **/
	@Query(value = "select t.id as EQUIPMENT_SAMPLE_ID,s.station_name, s.station_id, m.equipment_name, s.station_mn, t.equip_model "
			+ "from station s left join EQUIPMENT_SAMPLE t on s.station_mn = t.station_mn "
			+ "left join equipment_model m on t.equipment_model_id = m.id "
			+ "where s.station_id in (:stationIds)", nativeQuery = true)
	List<Map<String, Object>> queryBystauionId(@Param("stationIds") List<String> stationIds);

	/** 通过运维分公司筛选 **/
	@Query(value = "select station_id from station where OPER_COMPANY_ID = :companyId", nativeQuery = true)
	List<String> queryIdByCompanyId(@Param("companyId") String companyId);

	/** 通过站点负责人筛选 **/
	@Query(value = "select station_id from station where STATION_LEADER_ID = :userId", nativeQuery = true)
	List<String> queryIdByLeaderId(@Param("userId") String userId);

	/** 通过运维中心筛选 **/
	@Query(value = "select station_id from station where opcen_id = :opcenId", nativeQuery = true)
	List<String> queryIdByOpcenId(@Param("opcenId") String opcenId);

	/** 查询站点绑定人员的id **/
	@Query(value = "select s.user_id from SYS_USER_STATION s where s.station_id = (select t.station_id from STATION t where t.station_mn = :mn)", nativeQuery = true)
	List<String> queryUserIdByStationMn(String mn);

	/** 查询所有站点的mn **/
	@Query(value = "select t.station_mn from STATION t", nativeQuery = true)
	List<String> queryStationInfo();
}
